package m.system.db;

import m.common.model.util.QueryParameter;
import m.system.util.SystemUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作类
 * @author Administrator
 *
 */
public class DBManager {
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql) throws SQLException{
		return executeInsert(sql,new Object[]{});
	}
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql, Object... params) throws SQLException {
		return executeInsert(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql,Object[] params,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		Integer id=null;
		try {
			conn = TransactionManager.getConnection();
			ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			while (rs.next()){
				//生成的主键只有一个值，所以获得第一列的值即可
				id = rs.getInt(1);
			}
		} catch (SQLException e){
			SystemUtil.println("Error SQL:  ",sql);
			throw e;
		} finally {
			SystemUtil.println(System.currentTimeMillis()-t,"ms,Insert SQL:  ",sql);
			// 释放资源
			if(null!=ps) ps.close();
		}
		return id;
	}

	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql) throws SQLException{
		return executeUpdate(sql,new Object[]{});
	}
	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql, Object... params) throws SQLException {
		return executeUpdate(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql, Object[] params, int maxSeconds) throws SQLException {
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		int num;
		try {
			conn = TransactionManager.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行
			num = ps.executeUpdate();
		} catch (SQLException e){
			SystemUtil.println("Error SQL:  ",sql);
			throw e;
		} finally {
			SystemUtil.println(System.currentTimeMillis()-t,"ms,Update SQL:  ",sql);
			// 释放资源
			if(null!=ps) ps.close();
		}
		return num;
	}
	/**
	 * 批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String sql, List<Object[]> paramList) throws SQLException{
		batchUpdate(sql,paramList,DBConfig.getQueryTimeout());
	}
	/**
	 * 批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String sql, List<Object[]> paramList,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			for(Object[] params : paramList){
				if (params!=null) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i + 1, params[i]);
					}
					ps.addBatch();
				}
			}
			// 执行
			ps.executeBatch();
		} catch (SQLException e){
			SystemUtil.println("Error SQL:  ",sql);
			throw e;
		} finally {
			SystemUtil.println(System.currentTimeMillis()-t,"ms,BatchUpdate SQL:  ",sql);
			// 释放资源
			if(null!=ps) ps.close();
		}
	}
	/**
	 * 不同sql语句的批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String[] sqls) throws SQLException{
		batchUpdate(sqls,DBConfig.getQueryTimeout());
	}
	/**
	 * 不同sql语句的批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String[] sqls,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		for(String sql : sqls) SystemUtil.println("BatchUpdate SQL:  ",sql);
		Connection conn;
		Statement ps=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.createStatement();
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			for(String sql : sqls){
				if (sql!=null&&!"".equals(sql.trim())) {
					ps.addBatch(sql.trim());
				}
			}
			// 执行
			ps.executeBatch();
		} catch (SQLException e){
			SystemUtil.println("BatchUpdate Error!");
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
		}
		SystemUtil.println(System.currentTimeMillis()-t,"ms");
	}

	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(QueryParameter qp) throws SQLException {
		return executeQuery(qp.getSql(), qp.getValueList().toArray(new Object[] {}));
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql) throws SQLException {
		return executeQuery(sql,null);
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql, Object[] params) throws SQLException {
		return executeQuery(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql, Object[] params,int maxSeconds) throws SQLException {
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		ResultSetMetaData rsmd;
		ResultSet rs=null;
		int columnCount;
		List<Object> list = new ArrayList<>();
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			if (params!=null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行SQL获得结果集
			rs = ps.executeQuery();
			rsmd = rs.getMetaData();
			// 获得结果集列数
			columnCount = rsmd.getColumnCount();
			// 将ResultSet的结果保存到List中
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e){
			SystemUtil.println("Error SQL:  ",sql);
			throw e;
		} finally {
			SystemUtil.println(System.currentTimeMillis()-t,"ms,Query SQL:  ",sql);
			// 释放资源
			if(null!=ps) ps.close();
			if(null!=rs) rs.close();
		}
		return new DataSet(list);
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql) throws SQLException{
		return queryFirstRow(sql,new Object[]{});
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql, Object... params) throws SQLException{
		return queryFirstRow(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql, Object[] params,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		if(!sql.toLowerCase().contains("limit")) sql= sql + " LIMIT 1";
		Connection conn;
		PreparedStatement ps=null;
		ResultSetMetaData rsmd;
		ResultSet rs=null;
		int columnCount;
		Map<String, Object> map=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			if (params!=null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行SQL获得结果集
			rs = ps.executeQuery();
			rsmd = rs.getMetaData();
			// 获得结果集列数
			columnCount = rsmd.getColumnCount();
			// 将ResultSet的结果保存到List中
			if (rs.next()) {
				map = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
			}
		} catch (SQLException e){
			SystemUtil.println("Error SQL:  ",sql);
			throw e;
		} finally {
			SystemUtil.println(System.currentTimeMillis()-t,"ms,First SQL:  ",sql);
			// 释放资源
			if(null!=ps) ps.close();
			if(null!=rs) rs.close();
		}
		return null!=map?new DataRow(map):null;
	}
}
